#!/usr/bin/env python3
# -*- coding: utf-8 -*-

'测试'

from sqlalchemy import *
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()


class Student(Base):
    __tablename__ = 'student'

    id = Column(String(32), primary_key=True)
    name = Column(String(20))

    # 一个学生只能有一张校园卡
    card_id = Column(String(32), ForeignKey('card.id'))


class Card(Base):
    __tablename__ = 'card'

    id = Column(String(32), primary_key=True)
    money = Column(Integer)


class Teacher(Base):
    __tablename__ = 'teacher'

    id = Column(String(32), primary_key=True)
    name = Column(String(20))

    # 一个老师只能有一张校园卡
    card_id = Column(String(32), ForeignKey('card.id'))


class Course(Base):
    __tablename__ = 'course'

    id = Column(String(32), primary_key=True)
    name = Column(String(20))


class Student_Course(Base):
    __tablename__ = 'student_course'

    id = Column(Integer, primary_key=True)
    student_id = Column(String(32), ForeignKey('student.id'))
    course_id = Column(String(32), ForeignKey('course.id'))


class Teacher_Course(Base):
    __tablename__ = 'teacher_course'

    id = Column(String(32), primary_key=True)
    teacher_id = Column(String(32), ForeignKey('teacher.id'))
    course_id = Column(String(32), ForeignKey('course.id'))


# =====================================================

def getSession():
    try:
        # '数据库类型+数据库驱动名称://用户名:口令@机器地址:端口号/数据库名'
        engine = create_engine('mysql+mysqlconnector://root:root@localhost:3306/test')
        DBSession = sessionmaker(bind=engine)
        session = DBSession()
        print('Session Create Success!')
        return session
    except Exception as e:
        print('Create Session Error', e)


def closeSession(session):
    try:
        session.close()
        print('Session closed!')
    except Exception as e:
        print('Close Session Error', e)


def initData():
    session = getSession()
    try:
        # 一对一 【学生-校园卡】
        session.add(Student(id='20141202053', card_id='20141202053', name='毛毛'))
        session.add(Student(id='20141202048', card_id='20141202048', name='吉吉'))
        session.add(Student(id='20141202052', card_id='20141202052', name='盖盖'))
        session.add(Student(id='20141202078', card_id='20141202078', name='咪咪'))
        session.add(Student(id='20141202054', card_id='20141202054', name='德玛'))

        session.add(Card(id='20141202053', money=80))
        session.add(Card(id='20141202048', money=200))
        session.add(Card(id='20141202052', money=100))
        session.add(Card(id='20141202078', money=62))
        session.add(Card(id='20141202054', money=30))

        session.add(Course(id='1', name='语文'))
        session.add(Course(id='2', name='数学'))
        session.add(Course(id='3', name='英语'))

        session.add(Teacher(id='1001', name='王莹'))
        session.add(Teacher(id='1002', name='李小强'))
        session.add(Teacher(id='1003', name='张宇'))

        # 多对多 【老师-课程】
        session.add(Teacher_Course(id='1', teacher_id='1001', course_id='1'))
        session.add(Teacher_Course(id='2', teacher_id='1001', course_id='2'))
        session.add(Teacher_Course(id='3', teacher_id='1002', course_id='2'))
        session.add(Teacher_Course(id='4', teacher_id='1003', course_id='3'))

        # 多对多 【学生-课程】
        session.add(Student_Course(id='1', student_id='20141202053', course_id='1'))
        session.add(Student_Course(id='2', student_id='20141202053', course_id='2'))
        session.add(Student_Course(id='3', student_id='20141202053', course_id='3'))
        session.add(Student_Course(id='4', student_id='20141202048', course_id='2'))
        session.add(Student_Course(id='5', student_id='20141202052', course_id='1'))
        session.add(Student_Course(id='6', student_id='20141202052', course_id='2'))
        session.add(Student_Course(id='7', student_id='20141202054', course_id='3'))

        session.commit()
    except Exception as e:
        print(e)
        session.rollback()
    finally:
        closeSession(session)


def queryTest():
    try:
        session = getSession()
        maomao = session.query(Student).filter(Student.id == '20141202053').one()
        print(maomao.__dict__)

        mao_money = session.query(Card).filter(Student.id == '20141202053', Card.id == '20141202053').one()
        print(mao_money.money)

        mao_course = session.execute(
            'SELECT c.name FROM student_course sc JOIN student s JOIN course c WHERE sc.student_id = s.id AND sc.course_id = c.id AND s.id = :id',
            {'id': '20141202054'})
        print(list(map(lambda x: x[0], mao_course.fetchall())))
    except Exception as e:
        print(e)
    finally:
        closeSession(session)


if __name__ == '__main__':
    # initData()
    queryTest()
